package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

import server.Log.directOpt;

//to run this code, download MySQL Connector/J 5.0 from http://dev.mysql.com/downloads/connector/j/5.0.html
//extract the binary .jar from the archive, and add it to your java classpath !

/**
 * Handles all the communication between the diffrent program enteries (rules, protocols, logs) and the MYSQL database.
 */
public class MYSQLComm {

	/**
	 * Create a new MYSQL database connection.
	 * 
	 * @param hostname The hostname of the server. 
	 * @param dbname The database name of the server.
	 * @param username The user's name login.
	 * @param password The user's password.
	 * @param persistent true - The database connection will be persistent all the session. false - The connection will be established each command.
	 *  
	 * @throws SQLException An error connecting the MYSQL server occurred.
	 * @throws Exception An MSQL JDBC intance couldn't be created.
	 */
	public MYSQLComm(String hostname, String dbname, String username, String password, boolean persistent) throws SQLException, Exception {
		if (mysqlLoaded == false)
		{
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				mysqlLoaded = true;
			} catch (Exception e) {
				mysqlLoaded = false;
				throw e;
			}
		}
		this.hostname = hostname;
		this.dbname = dbname;
		this.persistent = persistent;
		this.username = username;
		this.password = password;
		if (persistent)
			connect();
	}

	/**
	 * Add a new rule to the database in an existing table.
	 * 
	 * @param table The DB table to add the rule to
	 * @param rule The rule to add
	 * 
	 * @throws SQLException Any SQL error
	 */
	public void addRule(String table, Rule rule) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		Savepoint save = null;
		try {
			conn.setAutoCommit(false);
			save = conn.setSavepoint();
			s.executeUpdate("UPDATE `" + table + "` SET `index` = `index` + 1 WHERE `index` >= " + rule.index + ";");
			if (rule.type == Rule.typeOpt.CONTEXT)
			{
				String port;
				if (rule.port == null || rule.port == -1)
					port = "NULL";
				else
					port = new Integer(rule.port).toString();
				if (rule.protocol == null || rule.permission == null)
					throw new SQLException("Illegal context rule definition");
				s.executeUpdate("INSERT INTO `" + table + "` (`index`, `type`, `port`, `protocol`, `permission`) VALUES " + "(" + rule.index + ", '" + rule.type + "', " + port + ", '" + rule.protocol + "', '" + rule.permission +"');");
			}
			else if (rule.type == Rule.typeOpt.IP)
			{
				String port;
				if (rule.port == null || rule.port == -1)
					port = "NULL";
				else
					port = new Integer(rule.port).toString();
				String iprange = (rule.iprange == null || rule.iprange.equals("")) ? "NULL" : "'" + rule.iprange + "'";
				if (rule.permission == null)
					throw new SQLException("Illegal ip rule definition");
				s.executeUpdate("INSERT INTO `" + table + "` (`index`, `type`, `ipRange`, `port`, `permission`) VALUES " + "(" + rule.index  + ", '" + rule.type + "', " + iprange + ", " + port + ", '" + rule.permission + "');");
			}
			else
				throw new SQLException("Illegal rule type");
			conn.commit();
		} catch (SQLException e) {
			conn.rollback(save);
			throw e;
		} finally {
			conn.setAutoCommit(true);
			s.close();
			disconnect();
		}
	}

	/**
	 * Delete a rule from the server in an existing table.
	 * 
	 * @param table The DB to delete the rule from
	 * @param index The index number of the rule in the table
	 * 
	 * @throws SQLException Any SQL error
	 */
	public void deleteRule(String table, int index) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			conn.setAutoCommit(false);
			savept = conn.setSavepoint();
			s.executeUpdate("DELETE FROM `" + table + "` WHERE `index` = " + index + ";");
			s.executeUpdate("UPDATE `" + table + "` SET `index` = `index` - 1 WHERE `index` > " + index + ";");
		} catch (SQLException e) {
			throw e;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Complete the rule deletion from the system, should be called after each deleteRule.
	 * 
	 * @param complete true - finish deletion, false - rollback before deleting (should be called on outside errors)
	 * 
	 * @throws SQLException Any SQL error
	 */
	public void deleteRuleComplete(boolean complete) throws SQLException
	{
		connect();
		try {
			if (complete)
				conn.commit();
			else
				conn.rollback(savept);
		} catch (SQLException e) {
			conn.rollback(savept);
		} finally {
			conn.setAutoCommit(true);
			savept = null;
			disconnect();
		}
	}

	/**
	 * Get the list of rules in a given table in the database.
	 * 
	 * @param table The name of the table
	 * 
	 * @return The list of rules in the table
	 * 
	 * @throws SQLException On any SQL error
	 */
	public Rule[] getRules(String table) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `index`, `type`, `iprange`, `port`, `protocol`, `permission` FROM `" + table + "` ORDER BY `index`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			Rule[] results = new Rule[size];
			int count = 0;
			while (rs.next())
			{
				Integer index = rs.getInt("index");
				String typeStr = rs.getString("type");
				Rule.typeOpt type;
				if (typeStr == null)
					type = null;
				else
					type = (typeStr.compareToIgnoreCase("IP") == 0) ? Rule.typeOpt.IP : Rule.typeOpt.CONTEXT;
				String iprange = rs.getString("iprange");
				Integer port = rs.getInt("port");
				String protocol = rs.getString("protocol");
				Rule.permissionOpt perm;
				String permStr = rs.getString("permission");
				if (permStr == null)
					perm = null;
				else
					perm = (permStr.compareToIgnoreCase("ALLOW") == 0) ? Rule.permissionOpt.ALLOW : Rule.permissionOpt.DENY;
				Rule rule = null;
				if (type == Rule.typeOpt.IP)
					rule = new Rule(index, iprange, port, perm);
				else
					rule = new Rule(index, port, protocol, perm);
				results[count] = rule;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Gets the password entry for a given user name.
	 * 
	 * @param user The user's name
	 * 
	 * @return The password entry including the user name, his password and privileges
	 * 
	 * @throws SQLException On any SQL error
	 */
	public Password getPassword(String user) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `user`, `password`, `type` FROM `Passwords` WHERE `user` = '" + user + "'");
			ResultSet rs = s.getResultSet();
			if (rs.next() == false)
				throw new SQLException("No such user");
			String username = rs.getString("user");
			String password = rs.getString("password");
			String typeStr = rs.getString("type");
			Password.typeOpt type;
			if (typeStr == null)
				type = Password.typeOpt.SIMPLE;
			else
				type = (rs.getString("type").compareToIgnoreCase("SIMPLE") == 0) ? Password.typeOpt.SIMPLE : Password.typeOpt.EXPERT;
			Password ret = new Password(username, password, type);
			rs.close();
			return ret;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Add a new protocol with regular expression definitions to the database.
	 * 
	 * @param protocol The protocol entry to add to the database
	 * 
	 * @throws SQLException On any SQL error
	 */
	public void addProtocol(Protocol protocol) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			String outReg = (protocol.outReg == null) ? "NULL" : "'" + protocol.outReg + "'";
			String inReg = (protocol.inReg == null) ? "NULL" : "'" + protocol.inReg + "'";
			s.executeUpdate("INSERT INTO `Protocols` (`protocol`, `outReg`, `inReg`) VALUES ('" + protocol.protocol + "', " + outReg + ", " + inReg + ");");
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Delets an existing protocol in the database.
	 * 
	 * @param name The name of the protocol to delete
	 * 
	 * @throws SQLException On any SQL error
	 */
	public void deleteProtocol(String name) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeUpdate("DELETE FROM `Protocols` WHERE `protocol` = '" + name + "';");
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Updates an existing protocol in the database.
	 * 
	 * @param protocol The updated protocol definition
	 * 
	 * @throws SQLException On any SQL error
	 */
	public void updateProtocol(Protocol protocol) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			String outReg = protocol.outReg == null ? "NULL" : "'" + protocol.outReg + "'";
			String inReg = protocol.inReg == null ? "NULL" : "'" + protocol.inReg + "'";
			s.executeUpdate("UPDATE `Protocols` SET `outReg` = " + outReg + ", `inReg` = " + inReg + " WHERE `protocol` = '" + protocol.protocol + "';");
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Get the protocol list that are in the database.
	 * 
	 * @return The list of protocols entries
	 * 
	 * @throws SQLException On any SQL error
	 */
	public Protocol[] getProtocols() throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `protocol`, `outReg`, `inReg` FROM `Protocols`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			Protocol[] results = new Protocol[size];
			int count = 0;
			while (rs.next())
			{
				String inReg = rs.getString("inReg");
				String outReg = rs.getString("outReg");
				String protocolName = rs.getString("protocol");
				Protocol protocol = new Protocol(protocolName, inReg, outReg);
				results[count] = protocol;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Get a specific protocol definition from the database.
	 * 
	 * @param name The name of the protocol to retrieve the information for
	 * 
	 * @return The protocol entry
	 * 
	 * @throws SQLException On any SQL error
	 */
	public Protocol getProtocol(String name) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT `protocol`, `outReg`, `inReg` FROM `Protocols` WHERE `protocol` = '" + name + "' ORDER BY `protocol`;");
			ResultSet rs = s.getResultSet();
			if (rs.next() == false)
				throw new SQLException("No such protocol");
			String inReg = rs.getString("inReg");
			String outReg = rs.getString("outReg");
			String protocolName = rs.getString("protocol");
			Protocol protocol = new Protocol(protocolName, inReg, outReg);
			rs.close();
			return protocol;
		} finally {
			s.close();
			disconnect();
		}

	}

	/**
	 * Adds a new empty ruleset entry in the database.
	 * 
	 * @param name The name of the ruleset
	 * 
	 * @throws SQLException On any SQL error
	 */
	public void createRuleSet(String name) throws SQLException
	{
		connect();		
		Statement s = conn.createStatement();
		try {
//			s.executeUpdate("CREATE TABLE RuleSet_" + name + "_In SELECT * FROM `ActiveRules_In`;");
//			s.executeUpdate("CREATE TABLE RuleSet_" + name + "_Out SELECT * FROM `ActiveRules_Out`;");
			s.executeUpdate("CREATE TABLE `RuleSet_" + name + "_In` LIKE `ActiveRules_In`;");
			s.executeUpdate("CREATE TABLE `RuleSet_" + name + "_Out` LIKE `ActiveRules_Out`;");
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Deletes a given ruleset from the database.
	 * 
	 * @param name The name of the ruleset
	 * 
	 * @throws SQLException On any SQL error
	 */
	public void deleteRuleSet(String name) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeUpdate("DROP TABLE IF EXISTS `RuleSet_" + name + "_In`;");
			s.executeUpdate("DROP TABLE IF EXISTS `RuleSet_" + name + "_Out`;");
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Get the list of rulesets in the database.
	 * 
	 * @return The names of the rulesets
	 * 
	 * @throws SQLException On any SQL error
	 */
	public String[] getRuleSets() throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SHOW TABLES LIKE 'RuleSet_%_In';");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			String[] results = new String[size];
			int count = 0;
			while (rs.next())
			{
				String name = rs.getString("Tables_in_" + dbname + " (RuleSet_%_In)");
				name = name.split("_")[1];
				results[count] = name;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Gets a log diary of all the packet logging that exist in the database and match the filtering options. Any option can be
	 * set to NULL with the meaning of search for ANY.
	 * 
	 * @param days The numbers of days back to look
	 * @param type The type of packets to look for
	 * @param protocol The protocol name to look for
	 * @param ip IP address to look for
	 * @param port Port number to look for
	 * @param dir Direction to look for
	 * 
	 * @return The list of log entries that match the filtering options
	 * 
	 * @throws SQLException On any SQL error
	 */
	public Log[] getLogs(Integer days, Log.typeOpt type, String protocol, String ip, Integer port, directOpt dir) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			String queryStr;
			queryStr = "SELECT UNIX_TIMESTAMP(`time`), `direct`, `sourceIp`, `destIp`, `sourcePort`, `destPort`, `protocol`, `rulePort`, `ruleProtocol`, `action` FROM `Log`";
			if (!(days == null && type == null && protocol == null && ip == null && port == null && dir == null))
			{
				String queryAnd = "";
				queryStr += " WHERE";
				if (days != null)
				{
					queryStr += " (DATEDIFF(CURRENT_DATE, `time`) < " + days + ")";
					queryAnd = " AND";
				}
				/*if (type != null)
				{
					queryStr += queryAnd + " (" + (type == Log.typeOpt.CONTEXT ? "`rulePortocol` != ''" : "`rulePortocol` = ''") +")";
					queryAnd = " AND";
				}*/
				if (protocol != null)
				{
					queryStr += queryAnd + " (`ruleProtocol` = '" + protocol +"')";
					queryAnd = " AND";
				}
				if (ip != null)
				{
					queryStr += queryAnd + " (`sourceIp` = '" + ip +"' OR `destIp` = '" + ip +"')";
					queryAnd = " AND";
				}
				if (port != null)
				{
					queryStr += queryAnd + " (`sourcePort` = '" + port +"' OR `destPort` = '" + port +"')";
					queryAnd = " AND";
				}
				if (dir != null)
				{
					queryStr += queryAnd + " (`direct` = '" + (dir == Log.directOpt.IN ? "IN" : "OUT") +"')";
					queryAnd = " AND";
				}
			}
			queryStr += " ORDER BY `time`;";
			s.executeQuery(queryStr);
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			Log[] results = new Log[size];
			int count = 0;
			while (rs.next())
			{
				Log log = new Log();
				log.time = rs.getLong("UNIX_TIMESTAMP(`time`)");
				//log.type = null;
				log.direct = (rs.getString("direct").compareToIgnoreCase("IN") == 0) ? Log.directOpt.IN : Log.directOpt.OUT;
				log.sourceIp = rs.getString("sourceIp");
				log.destIp = rs.getString("destIp");
				log.sourcePort = rs.getInt("sourcePort");
				log.destPort = rs.getInt("destPort");
				log.protocol = rs.getString("protocol");
				log.rulePort = rs.getInt("rulePort");
				log.ruleProtocol= rs.getString("ruleProtocol");
				log.action = (rs.getString("action").compareToIgnoreCase("ACCEPT")) == 0 ? Log.actionOpt.ACCEPT : Log.actionOpt.DENY;
				results[count] = log;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Returns the list of global system logs that exist in the database
	 * 
	 * @param days The number of days to look back
	 * 
	 * @return A list of Global logs entries that were found
	 * 
	 * @throws SQLException On any SQL error
	 */
	public GlobalLog[] getGlobalLogs(int days) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			s.executeQuery("SELECT UNIX_TIMESTAMP(`time`), `event` FROM `GlobalLog` WHERE (DATEDIFF(CURRENT_DATE, `time`) < " + days + ") ORDER BY `time`;");
			ResultSet rs = s.getResultSet();
			int size = getSize(rs);
			GlobalLog[] results = new GlobalLog[size];
			int count = 0;
			while (rs.next())
			{
				Long time = rs.getLong("UNIX_TIMESTAMP(`time`)");
				String event = rs.getString("event");
				GlobalLog log = new GlobalLog(time, event);
				results[count] = log;
				++count;
			}
			rs.close();
			return results;
		} finally {
			s.close();
			disconnect();
		}
	}

	/**
	 * Add a new global log message to the database
	 * 
	 * @param error The log message to add
	 * 
	 * @throws SQLException On any SQL error
	 */
	public void addGlobalLog(String error) throws SQLException
	{
		connect();
		Statement s = conn.createStatement();
		try {
			error.replace('\'', ' ');
			error.replace('`', ' ');
			s.executeUpdate("INSERT INTO `GlobalLog` (`time`, `event`) VALUES (NOW(), '" + error + "');");
		} finally {
			s.close();
			disconnect();
		}
	}

    protected MYSQLComm() {}    // empty ctor for testing

	/**
	 * Initiate a new connection to the database if one doesnt exist.
	 * 
	 * @throws SQLException On any SQL error
	 */
	protected void connect() throws SQLException
	{
		if (conn == null || conn.isClosed() == true)
		{
			conn = DriverManager.getConnection("jdbc:mysql://" + hostname + "/" + dbname, username, password);
		}
	}

	/**
	 * Disconnect an existing database connection.
	 * 
	 * @throws SQLException On any SQL error
	 */
	protected void disconnect() throws SQLException
	{
		if (conn != null && persistent == false)
		{
			if (!conn.isClosed())
				conn.close();
			conn = null;
		}
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#finalize()
	 */
	protected void finalize() throws Throwable {
		try {
			if (conn != null)
			{
				conn.close();
				conn = null;
			}
		} finally {
			super.finalize();
		}
	}

	/**
	 * Returns the number of entries in a given result set.
	 * 
	 * @param rs The result set to check
	 * 
	 * @return The number of entries in it
	 * 
	 * @throws SQLException On any SQL error
	 */
	protected int getSize(ResultSet rs) throws SQLException
	{
		rs.last();
		int size = rs.getRow();
		rs.beforeFirst();
		return size;
	}
	
	private static boolean mysqlLoaded = false;
	private String hostname;
	private String dbname;
	private boolean persistent;
	private String username;
	private String password;
	private Connection conn = null;
	private Savepoint savept = null;
	
}
